﻿'Imports Infragistics.Win.UltraActivityIndicator
'Imports Crystal.Common.Model
'Imports System.Data.SqlClient

'Public Class Form1

'    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'    End Sub

'    Private Sub GetDataFromExcel(ByVal isColumDupliacte As Boolean, ByRef dtInvestor As DataTable)
'        'Create the temporary table to store data
'        'Dim dtInvestor As New DataTable()
'        Dim myDataColumn As DataColumn
'        Dim myDataRow As DataRow
'        'MIN/MAX Values used to frame the working size of the Excel data to be imported.
'        Dim minCellRow As Integer = Int32.MaxValue
'        Dim maxCellRow As Integer = Int32.MinValue
'        Dim minCellColumn As Integer = Int32.MaxValue
'        Dim maxCellColumn As Integer = Int32.MinValue

'        Dim _SheetName As String
'        Dim _FileName As String

'        _FileName = "D:\Parminder\Projects\Crystal\Test Data\Investor Import\1700.xls"
'        _SheetName = "Sheet1"

'        Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
'        internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(_FileName)
'        For Each row As Infragistics.Documents.Excel.WorksheetRow In internalWorkBook.Worksheets(_SheetName).Rows
'            For Each cell As Infragistics.Documents.Excel.WorksheetCell In row.Cells
'                If cell.Value IsNot Nothing Then
'                    'Logic For Determining the Range of Rows/Columns in the Excel File.
'                    minCellRow = Math.Min(minCellRow, cell.RowIndex)
'                    maxCellRow = Math.Max(maxCellRow, cell.RowIndex)
'                    minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex)
'                    maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex)
'                End If
'            Next
'        Next

'        If isColumDupliacte = True Then

'            For i As Integer = minCellColumn To maxCellColumn
'                'Get the column name
'                Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()

'                'The export that was demonstrated earlier utilizes the first row
'                'for the column header.  We can now use that to give column names.
'                myDataColumn = New DataColumn(columnName)
'                dtInvestor.Columns.Add(myDataColumn)

'                ''Add the columns to the datatable.
'                'If myDataColumn.ColumnName = "LastReportDate" Then
'                '    myDataTable.Columns.Add(myDataColumn.ColumnName, GetType(DateTime))
'                'ElseIf myDataColumn.ColumnName = "EquityAssets" Then
'                '    myDataTable.Columns.Add(myDataColumn.ColumnName, GetType(Int64))
'                'Else
'                '    myDataTable.Columns.Add(myDataColumn)
'                'End If
'            Next

'        Else
'            For i As Integer = minCellColumn To maxCellColumn

'                Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()

'                'The export that was demonstrated earlier utilizes the first row
'                'for the column header.  We can now use that to give column names.
'                myDataColumn = New DataColumn(columnName)
'                dtInvestor.Columns.Add(myDataColumn)
'            Next

'            For rowIndex As Integer = minCellRow + 1 To maxCellRow
'                'Create a new DataRow
'                myDataRow = dtInvestor.NewRow()

'                'Loop through the columns and associate the value to each cell
'                For columnIndex As Integer = minCellColumn To maxCellColumn
'                    If internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value Is Nothing Then
'                        myDataRow(columnIndex) = DBNull.Value
'                    Else
'                        myDataRow(columnIndex) = internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value
'                    End If
'                Next

'                'Add The Row to a DataTable
'                dtInvestor.Rows.Add(myDataRow)
'            Next
'        End If
'        grdExcel.DataSource = dtInvestor


'    End Sub
'    Private strConfiguration As String = "Persist Security Info=False;database=RAMI;server=Parminder-PC\SQLEXPRESS;user id=sa;password=sa010#;Current Language=English;Connection Timeout=120"

'    Private Sub grdExcel_AfterRowActivate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles grdExcel.AfterRowActivate
'        'If grdExcel.Selected.Rows.Count <= 0 Then Exit Sub
'        Dim eReturnVal As ExecutionResult = Nothing
'        Dim ldtData As New DataTable
'        ' Try
'        Dim param(1) As SqlClient.SqlParameter
'        param(0) = New SqlParameter("@InvestorName", grdExcel.ActiveRow.Cells("InvestorName").Text)
'        param(1) = New SqlParameter("@ProjectID", 105)
'        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_SP_TestEDSSuggestion", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
'        ldtData.Load(CType(eReturnVal.ResultObject, IDataReader))
'        grdSuggestions.DataSource = ldtData
'        ldtData = Nothing
'    End Sub


'    'Public Function GetMappedColumns() As IDataReader

'    '    'Catch ex As Exception
'    '    '    Throw ex
'    '    'End Try
'    'End Function

'    Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click
'        GetDataFromExcel(False, New DataTable)
'    End Sub
'End Class

